from util.Sql_df import SQLServer_df
from util.sql_pyodbc import SQLServer_pyodbc

# 获取所有角色
def GetRoles():
    sql = f'SELECT role_id,role_name FROM roles'
    try:
        return SQLServer_df().Query(sql)
    except:
        return None

#  获取所有用户角色
def GetUser_roles():
    sql = f'''
        WITH UserRoles AS (
            SELECT
                users_id,
                STRING_AGG(role_name, ',') AS roles
            FROM
                user_roles AS a
                LEFT JOIN roles AS b ON a.role_id = b.role_id
            GROUP BY
                users_id
        )
        SELECT
            u.users_id,
            u.username,
            u.full_name,
            ur.roles
        FROM
            users AS u
            LEFT JOIN UserRoles AS ur ON u.users_id = ur.users_id;

    '''
    try:
        return SQLServer_df().Query(sql)
    except:
        return None

# 添加用户
def Add_user(username,fullname,pwd):
    sql = f'''
        INSERT INTO users (username,full_name,pwd)
        VALUES ('{username}','{fullname}','{pwd}');
    '''
    try:
        return SQLServer_pyodbc().execute(sql)
    except:
        return None

# 删除用户
def Delete_user(users_id):
    sqlList=[]
    sqlList.append(f'DELETE FROM users WHERE users_id={users_id}')
    sqlList.append(f'DELETE FROM user_roles WHERE users_id={users_id}')
    try:
        return SQLServer_pyodbc().execute_transaction(sqlList)
    except:
        return False

# 添加用户角色
def Add_user_roles(sqlList):
    try:
        return SQLServer_pyodbc().execute_transaction(sqlList)
    except:
        return False

        
